﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class AccountClassConverter
    {
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb"; //ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
        public static void ConvertAccountClass(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            DataTable DebtorGroupsDT = DataTables.CreateDebtorGroupsDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT AccountClassId, UCASE(ClassDescription) AS ClassDescription FROM AccountClass WHERE (UCASE(ClassDescription) <> 'NONE')");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            AccountClass accountClass = new AccountClass();
                            int i = 0;
                            accountClass.AccountClassId = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                            accountClass.ClassDescription = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            //SaveAccountClass(accountClass);
                            SavedebtorGroups(accountClass,DebtorGroupsDT);

                        if (DebtorGroupsDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, DebtorGroupsDT);
                        }
                    }
                    SaveAllDataTables(CpiamConnectionString, DebtorGroupsDT);
                }
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert debtorGroups ", ex);
                MessageBox.Show("Error converting debtorGroups: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable DebtorGroupsDT)
        {
            if (DebtorGroupsDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(DebtorGroupsDT, "DebtorGroups", ConnectionString);
        }
        /*
        private static void SaveAccountClass(AccountClass accountClass)
        {
            DebtorGroups debtorGroups = new DebtorGroups();
            debtorGroups.DebtGrID = Guid.NewGuid();
            debtorGroups.DebtGrpName = accountClass.ClassDescription;
            debtorGroups.DebtGrCode= accountClass.AccountClassId.ToString();
            INSERTDeptGroups(debtorGroups);
        }
        */
        private static void SavedebtorGroups(AccountClass accountClass, DataTable debtorGroupsDT)
        {
            DebtorGroups debtorGroups = new DebtorGroups(accountClass.ClassDescription,accountClass.AccountClassId.ToString());
            DebtorGroups.SaveDebtorGroupsToDataTable(debtorGroups, debtorGroupsDT);
        }

        /*
        private static void INSERTDeptGroups(DebtorGroups debtorGroups)
        {
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            try
            {
                dbConn.Open();
                using (SqlCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SET IDENTITY_INSERT DeptGroups ON INSERT INTO DeptGroups (DeptGroupID, DeptGroupName, DeptGroupNo) VALUES (@DeptGroupID,@DeptGroupName,@DeptGroupNo) SET IDENTITY_INSERT DeptGroups OFF");
                    command.Parameters.AddWithValue("DeptGroupID", debtorGroups.DebtGrID);
                    command.Parameters.AddWithValue("DeptGroupName", debtorGroups.DebtGrpName);
                    command.Parameters.AddWithValue("DeptGroupNo", debtorGroups.DebtGrCode);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }
         */
    }
}
